﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;

namespace sistemaAcademicoFinanceiroEscolas
{
    public partial class relatorioFinanceiroProfessor : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                SqlConnection sqlc = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
                sqlc.Open();

                string sqlProfessor = "select * from professor";

                SqlCommand cmdProfessor = new SqlCommand(sqlProfessor, sqlc);
                SqlDataReader myReaderProfessor = cmdProfessor.ExecuteReader();

                ListItem ProfessorSelecione = new ListItem();
                ProfessorSelecione.Value = "0";
                ProfessorSelecione.Text = "Selecione um Professor";

                lstProfessor.Items.Add(ProfessorSelecione);

                if (myReaderProfessor.HasRows)
                {

                    while (myReaderProfessor.Read())
                    {
                        ListItem Professor = new ListItem();

                        Professor.Value = myReaderProfessor["codigoProfessor"].ToString();
                        Professor.Text = myReaderProfessor["nomeProfessor"].ToString();

                        lstProfessor.Items.Add(Professor);
                    }

                }
                //Professor carregados
                myReaderProfessor.Close();
                cmdProfessor.Dispose();


                // fechando conexão com o banco de dados
                sqlc.Close();

                TxtDataInicial.Focus();
            }

        }

        void relatorio()
        {
            string dataInicial = TxtDataInicial.Text;
            string dataFinal = TxtDataFinal.Text;
            //string codigoProfessor = lstAluno.SelectedValue;
            //validarValores vV = new validarValores();

            TimeSpan dif = Convert.ToDateTime(dataFinal).Subtract(Convert.ToDateTime(dataInicial));

            if (dif.Days >= 0)
            {
                SqlConnection sqlp = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
                sqlp.Open();

                SqlConnection sqlpr = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
                sqlpr.Open();

                string selectProfessor = "";

                if (lstProfessor.SelectedValue == "0")
                {

                    //selectProfessor = " SELECT a.codigoAluno, a.horaInicialAula as horaInicialAula, ";
                    //selectProfessor += " a.horaFinalAula as horaFinalAula, a.codigoProfessor, ";

                    //selectProfessor += "(SELECT MIN(horaInicialAula) AS horaInicial FROM aula AS aa WHERE (codigoProfessor = a.codigoProfessor) AND (codigoAluno = a.codigoAluno) AND (dataAula = a.dataAula)) AS horaInicialIndividual, ";
                    //selectProfessor += "(SELECT MAX(horaFinalAula) AS horaFinal FROM aula AS aaa WHERE (codigoProfessor = a.codigoProfessor) AND (codigoAluno = a.codigoAluno) AND (dataAula = a.dataAula)) AS horaFinalIndividual, ";

                    //selectProfessor += "(SELECT SUM(CAST(valorHora AS decimal(12, 2))) AS valorTotal FROM aula as aaaa WHERE (codigoProfessor = a.codigoProfessor) AND (codigoAluno = a.codigoAluno) AND (dataAula = a.dataAula)) as valotTotalTotal, ";

                    //selectProfessor += "(SELECT SUM(CAST(valorPagoProfessor AS decimal(12, 2))) AS valorTotal FROM aula as aaaa WHERE (codigoProfessor = a.codigoProfessor) AND (codigoAluno = a.codigoAluno) AND (dataAula = a.dataAula)) as valorPagoTotal, ";

                    //selectProfessor += " a.valorTotal as valorTotal, a.valorPago as valorPago, p.codigoProfessor, p.nomeProfessor as nomeProfessor, p.nomeProfessor as nomeProfessor ";
                    //selectProfessor += " FROM aula a , aluno al, professor p ";

                    //selectProfessor += " WHERE ";
                    //selectProfessor += " a.codigoAluno = al.codigoAluno and ";
                    //selectProfessor += " a.codigoProfessor = p.codigoProfessor and ";
                    //selectProfessor += " convert(datetime,dataAula,103) ";
                    //selectProfessor += " BETWEEN convert(datetime,'" + dataInicial + "',103) AND convert(datetime,'" + dataFinal + "',103) order by p.nomeProfessor ";


                    selectProfessor = "SELECT        codigoAula, codigoProfessor, codigoAluno, codigoGrupo, dataAula, horaInicialAula, horaFinalAula, codigoDisciplina, descricaoAssuntoAula, observacoesAula,  ";
                    selectProfessor += "aulaRealizada, valorHora, valorTotal, falta, codigoAnoLetivo, linha, meiaHora, pago, valorPago, dataPagamento, valorTotalSemDesconto, valorTotalComDesconto,  ";
                    selectProfessor += "valorPagoProfessor, ";
                    selectProfessor += "(SELECT        MIN(horaInicialAula) AS horaInicial ";
                    selectProfessor += "FROM            aula AS aa ";
                    selectProfessor += "WHERE        (codigoProfessor = a.codigoProfessor) AND (codigoAluno = a.codigoAluno) AND (dataAula = a.dataAula)) AS horaInicialIndividual, ";
                    selectProfessor += "(SELECT        MAX(horaFinalAula) AS horaFinal ";
                    selectProfessor += "FROM            aula AS aaa ";
                    selectProfessor += "WHERE        (codigoProfessor = a.codigoProfessor) AND (codigoAluno = a.codigoAluno) AND (dataAula = a.dataAula)) AS horaFinalIndividual, ";
                    selectProfessor += "(SELECT        SUM(CAST(valorHora AS decimal(12, 2))) AS valorTotal ";
                    selectProfessor += "FROM            aula AS aaaa ";
                    selectProfessor += "WHERE        (codigoProfessor = a.codigoProfessor) AND (codigoAluno = a.codigoAluno) AND (dataAula = a.dataAula)) AS valotTotalTotal, ";
                    selectProfessor += "(SELECT        SUM(CAST(valorPagoProfessor AS decimal(12, 2))) AS valorTotal ";
                    selectProfessor += "FROM            aula AS aaaa ";
                    selectProfessor += "WHERE        (codigoProfessor = a.codigoProfessor) AND (codigoAluno = a.codigoAluno) AND (dataAula = a.dataAula)) AS valorPagoTotal, ";
                    selectProfessor += "(SELECT        MIN(horaInicialAula) AS horaInicial ";
                    selectProfessor += "FROM            aula AS aa ";
                    selectProfessor += "WHERE        (codigoProfessor = a.codigoProfessor) AND (codigoGrupo = a.codigoGrupo) AND (dataAula = a.dataAula)) AS horaInicialIndividualGrupo, ";
                    selectProfessor += "(SELECT        MAX(horaFinalAula) AS horaFinal ";
                    selectProfessor += "FROM            aula AS aaa ";
                    selectProfessor += "WHERE        (codigoProfessor = a.codigoProfessor) AND (codigoGrupo = a.codigoGrupo) AND (dataAula = a.dataAula)) AS horaFinalIndividualGrupo, ";
                    selectProfessor += "(SELECT        SUM(CAST(valorHora AS decimal(12, 2))) AS valorTotal ";
                    selectProfessor += "FROM            aula AS aaaa ";
                    selectProfessor += "WHERE        (codigoProfessor = a.codigoProfessor) AND (codigoGrupo = a.codigoGrupo) AND (dataAula = a.dataAula)) AS valotTotalTotalGrupo, ";
                    selectProfessor += "(SELECT        SUM(CAST(valorPagoProfessor AS decimal(12, 2))) AS valorTotal ";
                    selectProfessor += "FROM            aula AS aaaa ";
                    selectProfessor += "WHERE        (codigoProfessor = a.codigoProfessor) AND (codigoGrupo = a.codigoGrupo) AND (dataAula = a.dataAula)) AS valorPagoTotalGrupo, ";
                    selectProfessor += "(SELECT        COUNT(*) AS Expr1 ";
                    selectProfessor += "FROM            alunoGrupo ";
                    selectProfessor += "WHERE        (codigoGrupo = a.codigoGrupo)) AS quantidadeAlunosGrupo, (select nomeProfessor from professor where codigoProfessor = a.codigoProfessor) as nomeProfessor ";
                    selectProfessor += "FROM            aula AS a ";
                    selectProfessor += "WHERE        (CONVERT(datetime, dataAula, 103) BETWEEN CONVERT(datetime, '" + dataInicial + "', 103) AND CONVERT(datetime, '" + dataInicial + "', 103)) ";


                }
                else
                {

                    //selectProfessor =  " SELECT a.codigoAluno, a.horaInicialAula as horaInicialAula, a.horaFinalAula as horaFinalAula, ";
                    //selectProfessor += " a.valorTotal as valorTotal, a.valorPago as valorPago, a.codigoProfessor, p.codigoProfessor, p.nomeProfessor as nomeProfessor, ";

                    //selectProfessor += "(SELECT MIN(horaInicialAula) AS horaInicial FROM aula AS aa WHERE (codigoProfessor = a.codigoProfessor) AND (codigoAluno = a.codigoAluno) AND (dataAula = a.dataAula)) AS horaInicialIndividual, ";
                    //selectProfessor += "(SELECT MAX(horaFinalAula) AS horaFinal FROM aula AS aaa WHERE (codigoProfessor = a.codigoProfessor) AND (codigoAluno = a.codigoAluno) AND (dataAula = a.dataAula)) AS horaFinalIndividual, ";

                    //selectProfessor += "(SELECT SUM(CAST(valorHora AS decimal(12, 2))) AS valorTotal FROM aula as aaaa WHERE (codigoProfessor = a.codigoProfessor) AND (codigoAluno = a.codigoAluno) AND (dataAula = a.dataAula)) as valotTotalTotal, ";

                    //selectProfessor += "(SELECT SUM(CAST(valorPagoProfessor AS decimal(12, 2))) AS valorTotal FROM aula as aaaa WHERE (codigoProfessor = a.codigoProfessor) AND (codigoAluno = a.codigoAluno) AND (dataAula = a.dataAula)) as valorPagoTotal ";

                    //selectProfessor += " FROM aula a , aluno al , professor p ";

                    //selectProfessor += " WHERE ";
                    //selectProfessor += " a.codigoAluno = al.codigoAluno and ";

                    //selectProfessor += " a.codigoProfessor = p.codigoProfessor and ";

                    //selectProfessor += " a.codigoProfessor = " + lstProfessor.SelectedValue + " and  ";

                    //selectProfessor += " convert(datetime,dataAula,103) ";

                    //selectProfessor += " BETWEEN convert(datetime,'" + dataInicial + "',103) AND convert(datetime,'" + dataFinal + "',103) order by p.nomeProfessor ";


                    selectProfessor = "SELECT        codigoAula, codigoProfessor, codigoAluno, codigoGrupo, dataAula, horaInicialAula, horaFinalAula, codigoDisciplina, descricaoAssuntoAula, observacoesAula,  ";
                    selectProfessor += "aulaRealizada, valorHora, valorTotal, falta, codigoAnoLetivo, linha, meiaHora, pago, valorPago, dataPagamento, valorTotalSemDesconto, valorTotalComDesconto,  ";
                    selectProfessor += "valorPagoProfessor, ";
                    selectProfessor += "(SELECT        MIN(horaInicialAula) AS horaInicial ";
                    selectProfessor += "FROM            aula AS aa ";
                    selectProfessor += "WHERE        (codigoProfessor = a.codigoProfessor) AND (codigoAluno = a.codigoAluno) AND (dataAula = a.dataAula)) AS horaInicialIndividual, ";
                    selectProfessor += "(SELECT        MAX(horaFinalAula) AS horaFinal ";
                    selectProfessor += "FROM            aula AS aaa ";
                    selectProfessor += "WHERE        (codigoProfessor = a.codigoProfessor) AND (codigoAluno = a.codigoAluno) AND (dataAula = a.dataAula)) AS horaFinalIndividual, ";
                    selectProfessor += "(SELECT        SUM(CAST(valorHora AS decimal(12, 2))) AS valorTotal ";
                    selectProfessor += "FROM            aula AS aaaa ";
                    selectProfessor += "WHERE        (codigoProfessor = a.codigoProfessor) AND (codigoAluno = a.codigoAluno) AND (dataAula = a.dataAula)) AS valotTotalTotal, ";
                    selectProfessor += "(SELECT        SUM(CAST(valorPagoProfessor AS decimal(12, 2))) AS valorTotal ";
                    selectProfessor += "FROM            aula AS aaaa ";
                    selectProfessor += "WHERE        (codigoProfessor = a.codigoProfessor) AND (codigoAluno = a.codigoAluno) AND (dataAula = a.dataAula)) AS valorPagoTotal, ";
                    selectProfessor += "(SELECT        MIN(horaInicialAula) AS horaInicial ";
                    selectProfessor += "FROM            aula AS aa ";
                    selectProfessor += "WHERE        (codigoProfessor = a.codigoProfessor) AND (codigoGrupo = a.codigoGrupo) AND (dataAula = a.dataAula)) AS horaInicialIndividualGrupo, ";
                    selectProfessor += "(SELECT        MAX(horaFinalAula) AS horaFinal ";
                    selectProfessor += "FROM            aula AS aaa ";
                    selectProfessor += "WHERE        (codigoProfessor = a.codigoProfessor) AND (codigoGrupo = a.codigoGrupo) AND (dataAula = a.dataAula)) AS horaFinalIndividualGrupo, ";
                    selectProfessor += "(SELECT        SUM(CAST(valorHora AS decimal(12, 2))) AS valorTotal ";
                    selectProfessor += "FROM            aula AS aaaa ";
                    selectProfessor += "WHERE        (codigoProfessor = a.codigoProfessor) AND (codigoGrupo = a.codigoGrupo) AND (dataAula = a.dataAula)) AS valotTotalTotalGrupo, ";
                    selectProfessor += "(SELECT        SUM(CAST(valorPagoProfessor AS decimal(12, 2))) AS valorTotal ";
                    selectProfessor += "FROM            aula AS aaaa ";
                    selectProfessor += "WHERE        (codigoProfessor = a.codigoProfessor) AND (codigoGrupo = a.codigoGrupo) AND (dataAula = a.dataAula)) AS valorPagoTotalGrupo, ";
                    selectProfessor += "(SELECT        COUNT(*) AS Expr1 ";
                    selectProfessor += "FROM            alunoGrupo ";
                    selectProfessor += "WHERE        (codigoGrupo = a.codigoGrupo)) AS quantidadeAlunosGrupo, (select nomeProfessor from professor where codigoProfessor = a.codigoProfessor) as nomeProfessor ";
                    selectProfessor += "FROM            aula AS a ";
                    selectProfessor += "WHERE        (CONVERT(datetime, dataAula, 103) BETWEEN CONVERT(datetime, '" + dataInicial + "', 103) AND CONVERT(datetime, '" + dataInicial + "', 103)) ";
                    selectProfessor += " and a.codigoProfessor = " + lstProfessor.SelectedValue;

                }

                SqlCommand cmdProfessor = new SqlCommand(selectProfessor, sqlpr);
                SqlDataReader drProfessor = cmdProfessor.ExecuteReader();



                //essa string abaixo receberá o código html da tabela
                string tabela = "<table width=\"100%\" border=\"1\" cellpadding=\"0\" cellspacing=\"0\" bordercolor=\"#99CCFF\">";
                tabela += "<tr align=\"center\">";
                tabela += "<td align=\"center\" style=\"font-family:Verdana;font-size:12px;\">";
                tabela += "Professor";
                tabela += "</td>";
                tabela += "<td align=\"center\" style=\"font-family:Verdana;font-size:12px;\">";
                tabela += "Hora Inicial e Hora Final</td>";
                tabela += "</td>";
                tabela += "<td align=\"center\" style=\"font-family:Verdana;font-size:12px;\">";
                tabela += "Valor Total</td>";
                tabela += "<td align=\"center\" style=\"font-family:Verdana;font-size:12px;\">";
                tabela += "Valor Pago</td>";
                tabela += "<td align=\"center\" style=\"font-family:Verdana;font-size:12px;\">";
                tabela += "Status</td>";
                tabela += "</tr>";

                string codigoProfessor = "";
                string nomeProfessor = "";
                string horaInicialAula = "";
                string horaFinalAula = "";
                string valorTotal = "";
                string valorPago = "";
                //string Status = "";

                string codigoProfessorAluno = "";


                if (drProfessor.HasRows)
                {
                    while (drProfessor.Read())
                    {
                        if (codigoProfessorAluno != drProfessor["codigoProfessor"].ToString() + drProfessor["codigoAluno"].ToString() + drProfessor["codigoGrupo"].ToString())
                        {

                            codigoProfessor = drProfessor["codigoProfessor"].ToString();
                            nomeProfessor = drProfessor["nomeProfessor"].ToString();

                            string selectAula = "";

                            // SELECT PORCENTAGEM DO PROFESSOR

                            string selectPorcentagem = "";

                            if (drProfessor["codigoAluno"].ToString() != "")
                            {
                                selectPorcentagem = " SELECT codigoPorcentagem, valorPorcentagem, dataInicial, dataFinal, codigoProfessor, codigoModalidadeAula ";
                                selectPorcentagem += " FROM porcentagemProfessor where codigoPorcentagem = 1";

                                horaInicialAula = drProfessor["horaInicialIndividual"].ToString();
                                horaFinalAula = drProfessor["horaFinalIndividual"].ToString();
                                valorPago = drProfessor["valorPagoTotal"].ToString();
                                valorTotal = drProfessor["valotTotalTotal"].ToString();
                            }
                            else
                            {
                                selectPorcentagem = " SELECT codigoPorcentagem, valorPorcentagem, dataInicial, dataFinal, codigoProfessor, codigoModalidadeAula ";
                                selectPorcentagem += " FROM porcentagemProfessor where codigoPorcentagem = 2";

                                horaInicialAula = drProfessor["horaInicialIndividualGrupo"].ToString();
                                horaFinalAula = drProfessor["horaFinalIndividualGrupo"].ToString();
                                valorPago = drProfessor["valorPagoTotalGrupo"].ToString();
                                valorTotal = (Convert.ToDouble(drProfessor["valotTotalTotalGrupo"].ToString()) * Convert.ToDouble(drProfessor["quantidadeAlunosGrupo"].ToString())).ToString();
                            }



                            SqlCommand cmdProfessorPorcent = new SqlCommand(selectPorcentagem, sqlp);
                            SqlDataReader drProfessorPorcent = cmdProfessorPorcent.ExecuteReader();

                            // FIM SELECT PORCENTAGEM DO PROFESSOR

                            if (drProfessorPorcent.HasRows)
                            {
                                drProfessorPorcent.Read();

                                if (valorTotal == "")
                                {
                                    valorTotal = "0";
                                }

                                double valorTotalPorcentagem = (Convert.ToDouble(valorTotal) / 100) * Convert.ToDouble(drProfessorPorcent["valorPorcentagem"].ToString());

                                valorTotal = valorTotalPorcentagem.ToString();  //GUARDANDO NUMA VARIÁVEL O VALOR DA PORCENTAGEM

                            }


                            cmdProfessorPorcent.Dispose();
                            drProfessorPorcent.Close();


                            //AQUI CONTEUDO DO RELATÓRIO//

                            tabela += "<tr>";
                            tabela += "<td align=\"center\" style=\"font-family:Verdana; font-size:9px\">" + nomeProfessor + "</td>";
                            tabela += "<td align=\"center\" style=\"font-family:Verdana; font-size:9px\">" + horaInicialAula + " às " + horaFinalAula + " </td>";
                            tabela += "<td align=\"center\" style=\"font-family:Verdana; font-size:9px\">" + valorTotal + "</td>";
                            tabela += "<td align=\"center\" style=\"font-family:Verdana; font-size:9px\">" + valorPago + "</td>";


                            if (valorPago == "")
                            {
                                tabela += "<td style=\"background-color:#FF0000\"></td>"; // Vermelho

                            }
                            else if (Convert.ToDouble(valorPago) < Convert.ToDouble(valorTotal))
                            {
                                tabela += "<td style=\"background-color:#FFFF00\"></td>"; // Amarelo 
                            }
                            else
                            {
                                tabela += "<td style=\"background-color:#00FF00\"></td>"; // verde
                            }

                        }
                        
                        codigoProfessorAluno = drProfessor["codigoProfessor"].ToString() + drProfessor["codigoAluno"].ToString() + drProfessor["codigoGrupo"].ToString();

                    }

                }

                drProfessor.Close();
                cmdProfessor.Dispose();

                tabela += "</table>";
                Panel1.Controls.Add(new LiteralControl(tabela));

                sqlp.Close();
                sqlpr.Close();

            }

              //fechar conexao
            else
            {
                string literalcc = "<Script language=\"javascript\">function erro() {alert('A data final deve ser maior que a data inicial');}</script><script language=JavaScript>erro();</script>";
                Panel1.Controls.Add(new LiteralControl(literalcc));
            }

        }

        protected void btnGerar_Click(object sender, EventArgs e)
        {
            relatorio();
        }

    }
}